﻿using DataAccess;
using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
   public class BaoCaoDoanhSoDAO
    {
       public static List<BaoCaoDoanhSoDTO> baoCaoTheoThang(string thang)
       {
           List<BaoCaoDoanhSoDTO> list = new List<BaoCaoDoanhSoDTO>();
           string query = "select a.MaDaiLy, a.TenDaiLy, COUNT(b.MaPhieuXuat) as SoPhieuXuat, SUM(b.TongGiaTri) as TongTriGia from DaiLy a join PhieuXuat b on a.MaDaiLy = b.MaDaiLy where MONTH(b.NgayLapPhieu)= '" + thang + "' group by a.MaDaiLy, a.TenDaiLy";
           DataTable dt = DataConnection.ExecQuery(query);
           
           if (dt.Rows.Count > 0)
           {
               float tongtien=0;
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   tongtien = tongtien + float.Parse(dt.Rows[i]["TongTriGia"].ToString());
               }
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   DataRow dr = dt.Rows[i];
                   BaoCaoDoanhSoDTO bc = new BaoCaoDoanhSoDTO();
                   bc.TenDaiLy = dr["TenDaiLy"].ToString();
                   bc.SoPhieuXuat = dr["SoPhieuXuat"].GetHashCode();
                   bc.TongTriGia = dr["TongTriGia"].GetHashCode();
                   string ngay = DateTime.Now.ToShortDateString();
                   bc.ThangBaoCao = ngay;
                   float kq = (float)(bc.TongTriGia / tongtien) * 100;
                   bc.TiLe = (kq).ToString()+" %";
                   bc.STT = i+1;
                   list.Add(bc);
                   bc.MaDaiLy = dr["MaDaiLy"].ToString();
                   string them = "INSERT INTO BAOCAODOANHSO(MaDaiLy,TenDaiLy,SoPhieuXuat,TongTriGia,ThangBaoCao,TiLe) VALUES('" + bc.MaDaiLy + "','" + bc.TenDaiLy + "','" + bc.SoPhieuXuat + "','" + bc.TongTriGia + "','" + bc.ThangBaoCao + "','" + bc.TiLe + "')";
                   DataConnection.ExecNonQuery(them);
               }
           }
           return list;
       }
    }
}
